/*
Creates or upates the document described by the parameters.  Since the import could be coming from a
database that does not have the same key values for certain things, these keys may or may not be preserved.

The rules are:
1. If the document does not already exists in the target database, in any version, it will be created
2. If the document does already exist in the target database it may be imported or updated according to specific rules outlined below.

Preserving Keys (e.g., for Dev -> Test -> Production environments)

3. For Insert/Create only: if the user who created the document being imported does not exist in the import database, the default user key (that of the person doing the import) will be used.  Otherwise the CreatedByUserKey from the import record will be preserved.
4. If the user who last updated the document does not exist in the import database, the default user key (that of the person doing the import) will be used.  Otherwise the UpdatedByUserKey from the import record will be preserved.
5. If the user who last updated the status does not exist in the import database, the default user key (that of the person doing the import) will be used.  Otherwise the StatusUpdatedByUserKey from the import record will be preserved.
6. If the security AccessKey on the document being imported does not exist in the import database:
   a. and the document is only being imported into one folder and that folder has a DefaultChildAccessKey, the folders DefaultChildAccessKey will be used
   b. and the document is being imported into more than one folder or the only folder into which it is being imported has no DefaultChildAccessKey, the default access key (supplied at the start of the import process) will be used.  
Otherwise, the AccessKey from the import record will be preserved.
7. If the security DefaultChildAccessKey on the document being imported does not exist in the import database, NULL will be substituted.  Otherwise, the DefaultChildAccessKey from the import record will be preserved.
*/
CREATE PROCEDURE [dbo].[asi_DocumentImport]
   @DocumentKey uniqueidentifier,
   @DocumentName nvarchar(100),
   @DocumentDescription nvarchar(250),
   @DocumentVersionKey uniqueidentifier,
   @DocumentTypeCode nvarchar(3),
   @DocumentStatusCode int,
   @DocumentCode nvarchar(100),
   @AlternateName nvarchar(100),
   @IsSystem bit,
   @Blob image,
   @AccessKey uniqueidentifier,
   @DefaultChildAccessKey uniqueidentifier,
   @ContainsChildrenFlag bit,
   @BranchedFromDocumentKey uniqueidentifier,
   @RelatedDocumentVersionKey uniqueidentifier,
   @StatusUpdatedOn datetime,
   @StatusUpdatedByUserKey uniqueidentifier,
   @UpdatedOn datetime,
   @UpdatedByUserKey uniqueidentifier,
   @CreatedOn datetime,
   @CreatedByUserKey uniqueidentifier,
   @DefaultUserKey uniqueidentifier,
   @DefaultAccessKey uniqueidentifier,
   @Overwrite bit
AS
DECLARE
   @check int, @currentDocumentStatusCode int, @lockedOn datetime, @trackVersionsFlag bit
BEGIN
   -- some of the keys may not exist in the import database.  Check each and if they do not, use the default
   -- if the user who last updated the status does not exist in this database, use the default user key (person doing the import)
   SELECT @check = Count(*)
     FROM UserMain
    WHERE UserKey = @StatusUpdatedByUserKey

   IF @check = 0 SET @StatusUpdatedByUserKey = @DefaultUserKey
      
   -- if the user who last updated the document does not exist in this database, use the default user key (person doing the import)
   SELECT @check = Count(*)
     FROM UserMain
    WHERE UserKey = @UpdatedByUserKey

   IF @check = 0 SET @UpdatedByUserKey = @DefaultUserKey

   -- if the user who created the document does not exist in this database, use the default user key (person doing the import)
   SELECT @check = Count(*)
     FROM UserMain
    WHERE UserKey = @CreatedByUserKey

   IF @check = 0 SET @CreatedByUserKey = @DefaultUserKey

   -- if the AccessKey on the document does not exist, use the default access key
   SELECT @check = Count(*)
     FROM AccessMain
    WHERE AccessKey = @AccessKey

   IF @check = 0 SET @AccessKey = @DefaultAccessKey

   -- if the DefaultChildAccessKey on the document does not exist, set it to null
   IF @DefaultChildAccessKey IS NOT NULL
   BEGIN
      SELECT @check = Count(*)
        FROM AccessMain
       WHERE AccessKey = @DefaultChildAccessKey

      IF @check = 0 SET @DefaultChildAccessKey = null
   END

   -- if the RelatedDocumentVersionKey does not exists (it is a shortcut to nowhere)
   IF @RelatedDocumentVersionKey IS NOT NULL
   BEGIN
      SELECT @check = Count(*)
        FROM UniformRegistry
       WHERE UniformKey = @RelatedDocumentVersionKey

      IF @check = 0
      BEGIN
         RAISERROR ('Unable to import %s. Shortcut references a document that does not exist',11,1, @DocumentName)
         RETURN
      END
   END

   -- check to see if documents of this type track versions so we know if we need to follow any version tracking rules
   SELECT @trackVersionsFlag = TrackVersionsFlag
     FROM DocumentTypeRef
    WHERE DocumentTypeCode = @DocumentTypeCode

   -- next, check to see if this is an insert or an update and grab the DocumentStatusCode for later if update
   SELECT @currentDocumentStatusCode = DocumentStatusCode, @lockedOn = LockedOn
     FROM DocumentMain
    WHERE DocumentKey = @DocumentKey

   -- if there is not one there already, it is an insert
   IF @currentDocumentStatusCode IS NULL
   BEGIN
      INSERT INTO UniformRegistry (UniformKey, ComponentKey)
      SELECT @DocumentKey, ComponentKey
        FROM ComponentRegistry
       WHERE Name = 'Document'
         AND InterfaceName = 'BusinessController'

      -- check to see if the version key is already there
      SELECT @check = Count(*)
        FROM UniformRegistry
       WHERE UniformKey = @DocumentVersionKey

      -- if the DocumentVersionKey does not exist, add it
      IF @check = 0
         INSERT INTO UniformRegistry (UniformKey, ComponentKey)
         SELECT @DocumentVersionKey, ComponentKey
           FROM ComponentRegistry
          WHERE Name = 'DocumentVersion'
            AND InterfaceName = 'BusinessController'
      -- if the DocumentVersionKey DOES exists, we need to make sure the status rules are applied
      ELSE
      BEGIN
         -- If the incoming document's status is Working, make sure that if there is another working version
         -- it gets archived.  If the incoming document's status is Published, make sure that if there is another
         -- Published version, it gets archived
         IF (@DocumentStatusCode = 10 OR @DocumentStatusCode = 40) AND @trackVersionsFlag = 1
            UPDATE DocumentMain
               SET DocumentStatusCode = 50
             WHERE DocumentVersionKey = @DocumentVersionKey
               AND DocumentStatusCode = @DocumentStatusCode
      END

      INSERT INTO DocumentMain (
             DocumentKey,
             DocumentName,
             DocumentDescription,
             DocumentVersionKey,
             DocumentTypeCode,
             DocumentStatusCode,
             DocumentCode,
             AlternateName,
             IsSystem,
             Blob,
             AccessKey,
             DefaultChildAccessKey,
             ContainsChildrenFlag,
             BranchedFromDocumentKey,
             RelatedDocumentVersionKey,
             StatusUpdatedOn,
             StatusUpdatedByUserKey,
             UpdatedOn,
             UpdatedByUserKey,
             CreatedOn,
             CreatedByUserKey)
      VALUES (
             @DocumentKey,
             @DocumentName,
             @DocumentDescription,
             @DocumentVersionKey,
             @DocumentTypeCode,
             @DocumentStatusCode,
             @DocumentCode,
             @AlternateName,
             @IsSystem,
             @Blob,
             @AccessKey,
             @DefaultChildAccessKey,
             @ContainsChildrenFlag,
             @BranchedFromDocumentKey,
             @RelatedDocumentVersionKey,
             @StatusUpdatedOn,
             @StatusUpdatedByUserKey,
             @UpdatedOn,
             @UpdatedByUserKey,
             @CreatedOn,
             @CreatedByUserKey)
   END
   -- there is already one there, it is an update
   ELSE
   BEGIN
      IF @Overwrite = 1
      BEGIN
         -- if the existing document's status is Publish Requested, PublishPending, RecycleRequested, or RecyclePending
         -- or the document is Locked, we can't import.  Raise an error
         IF @currentDocumentStatusCode IN (20,30,60,70)
         BEGIN
            DECLARE @DocumentStatusDesc nvarchar(30)
            SELECT @DocumentStatusDesc = DocumentStatusDesc FROM DocumentStatusRef WHERE DocumentStatusCode = @currentDocumentStatusCode

            RAISERROR ('Unable to import %s. The exact version of the document already exists in the target database with a status of %s.',11,1, @DocumentName, @DocumentStatusDesc)
            RETURN
         END

         IF @lockedOn IS NOT NULL
         BEGIN
            RAISERROR ('Unable to import %s. The exact version of the document already exists in the target database and is Locked for editing.',11,1, @DocumentName)
            RETURN
         END

         -- if the status of the doc being imported does not match the one that exists, we need to apply
         -- some special rules.
         IF @currentDocumentStatusCode <> @DocumentStatusCode AND @trackVersionsFlag = 1
         BEGIN
            -- if source is Published and Target is Working, need to archive existing published version first
            IF @DocumentStatusCode = 40 AND @currentDocumentStatusCode = 10
               UPDATE DocumentMain
                  SET DocumentStatusCode = 50
                WHERE DocumentVersionKey = @DocumentVersionKey
                  AND DocumentStatusCode = @DocumentStatusCode

            -- for all other combinations we have not yet covered except Source is Archived, Target is Working
            -- leave the status unchanged
            IF (@DocumentStatusCode = 10 AND @currentDocumentStatusCode IN (40,50)) OR
               (@DocumentStatusCode = 40 AND @currentDocumentStatusCode = 50) OR
               (@DocumentStatusCode = 50 AND @currentDocumentStatusCode = 40)
               SET @DocumentStatusCode = @currentDocumentStatusCode
         END

         UPDATE DocumentMain
            SET DocumentName = @DocumentName,
                DocumentDescription = @DocumentDescription,
                DocumentTypeCode = @DocumentTypeCode,
                DocumentStatusCode = @DocumentStatusCode,
                DocumentCode = @DocumentCode,
                AlternateName = @AlternateName,
                IsSystem = @IsSystem,
                Blob = @Blob,
                DefaultChildAccessKey = @DefaultChildAccessKey,
                ContainsChildrenFlag = @ContainsChildrenFlag,
                BranchedFromDocumentKey = @BranchedFromDocumentKey,
                RelatedDocumentVersionKey  = @RelatedDocumentVersionKey,
                StatusUpdatedOn = @StatusUpdatedOn,
                StatusUpdatedByUserKey = @StatusUpdatedByUserKey,
                UpdatedOn = @UpdatedOn,
                UpdatedByUserKey = @UpdatedByUserKey
          WHERE DocumentKey = @DocumentKey
      END
   END
   SELECT @DocumentKey
END

GO